<?php

Class Mso extends CI_Model
{
	function __construnct()
	{
		$this->load->database();
	}

	function getbarang($term)
	{
		$sql = "select * from produk where nama like '%$term%'";
		return $this->db->query($sql);
	}

	function tipetrn()
	{
		$sql = "select * from tipetrn";
		//echo $sql;
		return $this->db->query($sql)->result();
	}

	function saleid()
	{
		$sql     = "select max(no) as maxno from sale";
		$row     = $this->db->query($sql)->row();
		$maxno   = $row->maxno;

		$year    = date("y");
		$bln     = date("n");
        
		$noso    = sprintf("$year$bln%08d",$maxno + 1);

		return $noso;
	}

	function nofaktur()
	{
		$sql = "select max(no) as maxno from sale";
		$row = $this->db->query($sql)->row();
		$maxno  = $row->maxno;
		$rbulan  = array(1=>"I","II","III","IV","V","VI","VII","VIII","IX","X","XI","XII");
		$bln     = date("n");
		//echo $bln;

		$bulan   = $rbulan[12];
		$year    = date("y");
		$faktur = sprintf("%08d/SNB/$bulan/$year",$maxno + 1);

		return $faktur;
	}

    function cek($saleid){
        $sql = "select saleid from sale where saleid = '$saleid'";
        return $this->db->query($sql)->num_rows();
    }
    
	function sale($saleid,$kepada,$untuk,$sales,$tender,$jenis,$tanggal)
	{
		$petugas = $this->session->userdata('username');

		$tanggal = date("Y-m-d",strtotime($tanggal));
		$now     = date("Y-m-d H:i:s");
        
        $stat    = 1;
		$sql     = "insert into sale(saleid,pelanggan1,jenis,tender,sales,pelanggan2,so_date,username)
		           values('$saleid','$kepada','$jenis','$tender','$sales','$untuk','NOW()','$petugas')";
		//echo $sql;exit;
		if($this->db->query($sql))
            return 1;
        else
            return 0;
     }
     
     
     function sale_detail($saleid,$prokode,$phg,$price,$qty,$diskon,$total,$stat){
        //ambil harga beli
	    $query = "select hnappn from produk where prokode = '$prokode'";
        $row   = $this->db->query($query)->row();
        $hpp   = $row->hnappn;
        //harga beli * qty
        $hppxqty   = $hpp * $qty;        
        //harga jual * qty
        $pricexqty = $price * $qty;
        //ambil diskon rp dari harga jual setelah dikalikan dengan qty
        $diskonrp  = $pricexqty * ($diskon/100);
        //hitung total setelah dipotong diskon
        $total     = $pricexqty - $diskonrp;
        //ambil ppn dari total
		$ppn       = $total * 0.1;
        //hitung total setelah di tambah ppn
        $totalppn  = $total * 1.1;
        
        $now = date("Y-m-d H:i:s");
		$sql  = "insert into sale_detail(saleid,prokode,hpp,price,qty,diskon,diskon_rp,pricexqty,total,trx_date,phg,hppxqty)
				values('$saleid','$prokode','$hpp','$price','$qty','$diskon','$diskonrp','$pricexqty','$total','$now','$phg','$hppxqty')";
        if($this->db->query($sql)) 
            $stat *= 1;
        else
            $stat *= 0;

		$sql     = "update sale set  total_order = total_order  + '$pricexqty',
                                     diskon      = diskon + '$diskonrp',
                                     netto       = netto  + '$total',
                                     ppn         = ppn + '$ppn',
                                     nettoppn    = nettoppn + '$totalppn' 
                                     where saleid  = '$saleid'";
        if($this->db->query($sql)) 
            $stat *= 1;
        else
            $stat *= 0;
            
        
        return $stat;
    }

	function update_sale_detail($no,$saleid,$prokode,$phg,$price,$qty,$diskon,$total,$stat)
	{
	    $query = "select b.hnappn from sale_detail a,produk b where a.prokode=b.prokode and a.no = '$no'";
        $row   = $this->db->query($query)->row();
        $hpp   = $row->hnappn;
        //harga beli * qty
        $hppxqty   = $hpp * $qty;        
        //harga jual * qty
        $pricexqty = $price * $qty;
        //ambil diskon rp dari harga jual setelah dikalikan dengan qty
        $diskonrp  = $pricexqty * ($diskon/100);
        //hitung total setelah dipotong diskon
        $total     = $pricexqty - $diskonrp;
        //ambil ppn dari total
		$ppn       = $total * 0.1;
        //hitung total setelah di tambah ppn
        $totalppn  = $total * 1.1;
        
		$now  = date("Y-m-d H:i:s");
		$sql  = "update sale_detail set 
                                    price     = '$price',
                                    qty       = '$qty',
                                    diskon    = '$diskon',
                                    diskon_rp = '$diskonrp',
                                    total     = '$total',
                                    hppxqty   = '$hppxqty',
                                    pricexqty = '$pricexqty',
                                    phg       = '$phg'
                                    where no  = '$no'";
        if($this->db->query($sql)) 
            $stat *= 1;
        else
            $stat *= 0;

		
        $sql   = "select sum(pricexqty) as total_order,
                         sum(diskon_rp) as diskon,
                         sum(pricexqty - diskon_rp) as netto,
                         sum(total) as total 
                    from sale_detail where saleid = '$saleid'";
		$row      = $this->db->query($sql)->row();

        $total_order = $row->total_order; 
        $ppn      = $total_order * 0.1;
        $diskon   = $row->diskon;
        $netto    = $row->netto;
        $nettoppn = $netto + $ppn;
		$sql      = "update sale set total_order  = '$total_order',
                                   diskon     = '$diskon',
                                   netto        = '$netto',
                                   ppn          = '$ppn',
                                   nettoppn     = '$nettoppn' 
                                   where saleid = '$saleid'";

        if($this->db->query($sql)) 
            $stat *= 1;
        else
            $stat *= 0;
            
        
        return $stat;
	}

	function get_barang($prokode)
	{
		$sql = "select * from produk where prokode = '$prokode'";
		return $this->db->query($sql)->row();
	}

	function get_sale($saleid)
	{
		$sql = "SELECT  a.no,
				a.saleid,
				a.nofaktur,
                a.pelanggan1,
                a.pelanggan2,
				(SELECT nama FROM pelanggan WHERE pelanggan_id = a.pelanggan1) AS kepada,
                pg1.alamat as alamat1,
                pg1.kota as kota1,
                pg1.tlp as tlp1,
                pg1.npwp as npwp1,
                pg2.alamat as alamat2,
                pg2.kota as kota2,
                pg2.tlp as tlp2,
                pg2.npwp as npwp2,
				(SELECT nama FROM pelanggan WHERE pelanggan_id = a.pelanggan2) AS untuk,
				a.so_date,
				a.pm,
				a.jatuh_tempo,
				a.sales,
				b.nama as nama_sales,	
				a.total_order,
				a.diskon,
				a.netto,
				a.ppn,
                b.alamat as alamat3,
                b.kota as kota3,
                b.tlp as tlp3,
                b.npwp as npwp3,
				a.nettoppn,a.trx_date,a.jenis,a.tender,a.status	
				FROM sale a
                left join pelanggan pg1 on a.pelanggan1 = pg1.pelanggan_id
                left join pelanggan pg2 on a.pelanggan2 = pg2.pelanggan_id
				LEFT JOIN sales b ON a.sales=b.sales_id
				LEFT JOIN tipetrn c ON a.jenis=c.no where saleid = '$saleid'";
        //echo $sql;
		return $this->db->query($sql);
	}

	function get_sale_detail($saleid)
	{
		$sql = "SELECT sd.no,sd.saleid,sd.prokode,pd.nama,sd.price,sd.diskon,sd.total,pb.nama AS nama_pabrik,sd.qty,pd.satuan,bkb.keterangan,bkb.qty,bkb.nobatch,sd.phg 
                FROM sale_detail sd
                LEFT JOIN bkb_detail bkb ON sd.saleid=bkb.saleid AND sd.prokode=bkb.prokode
                LEFT JOIN produk pd ON sd.prokode=pd.prokode
                LEFT JOIN pabrik pb ON pd.pabrik=pb.pabrik_id
                WHERE sd.saleid='$saleid'";//echo $sql;
		return $this->db->query($sql);
	}
    
	function getpelanggan($kode)
	{
		$sql = "select * from pelanggan where pelanggan_id = '$kode'";
		return $this->db->query($sql)->row();		
	}

	function ceknoso($noso)
	{
		$sql = "select count('$noso') as ada from sale where saleid = '$noso'";
		$row = $this->db->query($sql)->row();

		return $row->ada;
	}

	function ceknofaktur($nofaktur)
	{
		$sql = "select count('$nofaktur') as ada from sale where nofaktur = '$nofaktur'";
		$row = $this->db->query($sql)->row();

		return $row->ada;
	}

	function getrowfakturdetail($no)
	{
		$sql = "select * from sale_detail where no = '$no'";
		return $this->db->query($sql)->row();		
	}

	function hapus($no)
	{
		$sql   = "select total from sale_detail where no = '$no'";
		$res   = $this->db->query($sql)->row();
		$total = $res->total;
		//echo $total;exit;

		$sql1  = "update sale set total_1 = total_1 - '$total'";
		//echo $sql1;exit;
		$this->db->query($sql1);

		$sql2  = "delete from sale_detail where no = '$no'";
		return $this->db->query($sql2); 
	}
	
	function tampilnonteder()
	{
		$sql = "SELECT  a.no,
				a.saleid,
				a.nofaktur,
                a.pelanggan1,
                a.pelanggan2,
				(SELECT nama FROM pelanggan WHERE pelanggan_id = a.pelanggan1) AS kepada,
                pg1.alamat as alamat1,
                pg1.kota as kota1,
                pg1.tlp as tlp1,
                pg1.npwp as npwp1,
                pg2.alamat as alamat2,
                pg2.kota as kota2,
                pg2.tlp as tlp2,
                pg2.npwp as npwp2,
				(SELECT nama FROM pelanggan WHERE pelanggan_id = a.pelanggan2) AS untuk,
				a.so_date,
				a.pm,
				a.jatuh_tempo,
				a.sales,
                a.status,
				b.nama as nama_sales,	
				a.total_order,
				a.diskon,
				a.netto,
				a.ppn,
                b.alamat as alamat3,
                b.kota as kota3,
                b.tlp as tlp3,
                b.npwp as npwp3,
				a.nettoppn,a.trx_date,a.jenis,a.tender,a.status	
				FROM sale a
                left join pelanggan pg1 on a.pelanggan1 = pg1.pelanggan_id
                left join pelanggan pg2 on a.pelanggan2 = pg2.pelanggan_id
				LEFT JOIN sales b ON a.sales=b.sales_id
				LEFT JOIN tipetrn c ON a.jenis=c.no";
		return $this->db->query($sql)->result();
	}
    
    function pilih_harga($phg,$prokode)
    {
        $sql = "select harga$phg from produk where prokode = '$prokode'";
        //echo $sql;
        $row = $this->db->query($sql)->row();
        
        if($phg == '1'){
            return $row->harga1;
        }else if($phg == '2'){
            return $row->harga2;
        }else if($phg == '3'){
            return $row->harga3;
        }else if($phg == '4'){
            return $row->harga4;
        }else{
            return $row->harga5;
        }
    }
    
    function detail_so($id)
    {
		$sql = "SELECT  a.no,
				a.saleid,
				a.nofaktur,
                a.pelanggan1,
                a.pelanggan2,
				(SELECT nama FROM pelanggan WHERE pelanggan_id = a.pelanggan1) AS kepada,
				(SELECT nama FROM pelanggan WHERE pelanggan_id = a.pelanggan2) AS untuk,
				a.kdok,
				a.nodok,
				a.tanggal,
				a.noso,
				a.cbayar,
				a.jatuh_tempo,
				a.sales_id,
				b.nama as nama_sales,	
				a.pot_extra,
				a.pot_tunai,
				a.total_1,
				a.potongan,
				a.total_2,
				a.ppn,a.b_kirim,a.materai,
				a.total,a.trx_date,a.tipetrn,a.tender,a.askes,a.petugas,a.username,a.kerjasama,a.status	
				FROM sale a
				LEFT JOIN sales b ON a.sales_id=b.sales_id
				LEFT JOIN tipetrn c ON a.tipetrn=c.no where saleid = '$id '";
        return $this->db->query($sql)->row();
    }

    function detail_so_bykey($key)
    {
        $keytanggal = date("Y-m-d",strtotime('$key'));
        
		$sql = "SELECT  a.no,
				a.saleid,
				a.nofaktur,
                a.pelanggan1,
                a.pelanggan2,
				(SELECT nama FROM pelanggan WHERE pelanggan_id = a.pelanggan1) AS kepada,
				(SELECT nama FROM pelanggan WHERE pelanggan_id = a.pelanggan2) AS untuk,
				a.kdok,
				a.nodok,
				a.tanggal,
				a.noso,
				a.cbayar,
				a.jatuh_tempo,
				a.sales_id,
				b.nama as nama_sales,	
				a.pot_extra,
				a.pot_tunai,
				a.total_1,
				a.potongan,
				a.total_2,
				a.ppn,a.b_kirim,a.materai,
				a.total,a.trx_date,a.tipetrn,a.tender,a.askes,a.petugas,a.username,a.kerjasama,a.status	
				FROM sale a
				LEFT JOIN sales b ON a.sales_id=b.sales_id
				LEFT JOIN tipetrn c ON a.tipetrn=c.no 
                where (a.saleid like '$key%' or a.nofaktur like '$key%' or tanggal like '$keytanggal%' or jatuh_tempo like '$keytanggal%')";
        //echo $sql;exit;
        return $this->db->query($sql)->row();
    }

/*
######################################################### Sales Order Out ##########################################################################
*/
    //sales order out
	function simpan_out_detail($saleid,$prokode,$harga2,$qty2,$diskon2,$total2)
    {

		$pricexqty = $harga2*$qty2;
        $diskonrp  = $pricexqty * ($diskon2/100);

		$sql       = "update sale_detail set qty = qty - '$qty2',price='$harga2',diskon='$diskon2',total='$total2' where prokode = '$prokode' and saleid = '$saleid'";
        return $this->db->query($sql);
        //echo $sql."<br />";		
	}
    
    function  simpan_out($saleid,$total_1nya,$potongannya,$total_2nya,$ppnnya,$totalnya,$status){
        $sql = "update sale set total_1  = '$total_1nya',
                                potongan = '$potongannya',
                                total_2  = '$total_2nya',
                                ppn      = '$ppnnya',
                                total    = '$totalnya',
                                status   = '$status'
                where saleid = '$saleid'";
        return $this->db->query($sql);
        //echo $sql."<br />";	
    }

}